package com.aierkeji.call.cms.dao.jpa.custom.impl;

import java.math.BigInteger;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.internal.SessionImpl;
import org.hibernate.query.NativeQuery;
import org.hibernate.query.Query;
import org.hibernate.query.spi.QueryImplementor;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate5.HibernateCallback;
import org.springframework.orm.hibernate5.HibernateTemplate;

import com.aierkeji.call.cms.dao.jpa.custom.AkjCallReportDaoCustom;
import com.aierkeji.call.cms.entity.vo.AkjCallClueStatisticsVo;
import com.aierkeji.call.cms.entity.vo.AkjCallClueVo;
import com.aierkeji.call.cms.util.BeanPropertyRowsMapper;
import com.aierkeji.call.cms.util.DateTimeUtils;
import com.aierkeji.call.cms.util.PageUtils;

public class AkjCallReportDaoCustomImpl implements AkjCallReportDaoCustom {

	@Autowired
	JdbcTemplate jdbcTemplate;

	@PersistenceContext
	private EntityManager em;
	@Autowired
	HibernateTemplate hibernateTemplate;

	String baseSelectSQL = "";
	@PostConstruct
	public void init() {
		baseSelectSQL = " sum(t.clue_total) as clueTotal," + 
				"sum(t.call_time) as callTime," + 
				"sum(t.called_total) as calledTotal," + 
				"sum(t.connect_total) as connectTotal," + 
				"sum(t.un_connect_total) as unConnectTotal," + 
				"sum(t.un_confirm_total) as unConfirmTotal," + 
				"sum(t.confirm_total) as confirmTotal," + 
				"sum(t.wait_total) as waitTotal," + 
				"sum(t.net_called_total) as netCalledTotal," + 
				"sum(t.net_connect_total) as netConnectTotal";
	}

	public String getwhere(Date staticDate, Long assigneTo, Long learId, List<Object> list, Long clueGroupId) {
		String hql = "";
		int index = 1;
		if (staticDate != null) {
			LocalDateTime dateTime = LocalDateTime.ofInstant(staticDate.toInstant(), ZoneId.systemDefault());
			int year = dateTime.getYear();
			int month = dateTime.getMonthValue();
			int day = dateTime.getDayOfMonth();
			hql += " and t.year = ?" + (index++) + " and t.month = ?" + (index++) + " and t.day = ?" + (index++);
			list.add((long) year);
			list.add((long) month);
			list.add((long) day);
		}
		if (assigneTo != null && assigneTo > 0) {
			hql += " and t.call_user_id = ?" + (index++);
			list.add(assigneTo);
		}
		if (learId != null && learId > 0) {
			hql += " and a.leader_id = ?" + (index++);
			list.add(learId);
		}
		if (clueGroupId != null && clueGroupId > 0) {
			hql += " and t.group_id = ?" + (index++);
			list.add(clueGroupId);
		}
		return hql;
	}
	
	

	public Page<AkjCallClueStatisticsVo> getReport(Date staticDate, Long assigneTo, Long leaderId, Long clueGroupId,
			int pageNo, int pageSize) {
		String day = DateTimeUtils.converDateToString(staticDate, DateTimeUtils.DATE_PATTERN_DAY);
		String hql2 = "select " + "'" + day + "' as statDate," +baseSelectSQL + ",t.group_id as groupId, " + "a.name as name "
				+ "from akj_call_statistics t join akj_call_user a on t.call_user_id = a.id where a.is_delete=0 ";
		String countsql2 = "select distinct(1) as cCount from akj_call_statistics t join akj_call_user a on t.call_user_id = a.id where a.is_delete=0";

		List<Object> list2 = new ArrayList<Object>();
		String where = this.getwhere(staticDate, assigneTo, leaderId, list2, clueGroupId);
		hql2 += where + " order by t.call_user_id";
		countsql2 += where + "";
		return query(hql2, countsql2, list2, pageNo, pageSize);
	}

	private void addScalar(NativeQuery query) {
		query.addScalar("statDate", StandardBasicTypes.STRING);
		query.addScalar("clueTotal", StandardBasicTypes.LONG);
		query.addScalar("callTime", StandardBasicTypes.LONG);
		query.addScalar("calledTotal", StandardBasicTypes.LONG);
		query.addScalar("connectTotal", StandardBasicTypes.LONG);
		query.addScalar("unConnectTotal", StandardBasicTypes.LONG);
		query.addScalar("unConfirmTotal", StandardBasicTypes.LONG);
		query.addScalar("confirmTotal", StandardBasicTypes.LONG);
		query.addScalar("waitTotal", StandardBasicTypes.LONG);
		query.addScalar("netCalledTotal", StandardBasicTypes.LONG);
		query.addScalar("netConnectTotal", StandardBasicTypes.LONG);
		query.addScalar("groupId", StandardBasicTypes.LONG);
		query.addScalar("name", StandardBasicTypes.STRING);
	}

	public Page<AkjCallClueStatisticsVo> getSubReport(Date staticDate, Long assigneTo, Long leaderId, Long clueGroupId,
			int pageNo, int pageSize) {
		String day = DateTimeUtils.converDateToString(staticDate, DateTimeUtils.DATE_PATTERN_DAY);
		String hql2 = "select " + "'" + day + "' as statDate," + baseSelectSQL  + ",t.group_id as groupId, " + "a.name as name "
				+ "from akj_call_statistics t join akj_call_user a on t.call_user_id = a.id where a.is_delete=0 ";
		String countsql2 = "select count(distinct(t.call_user_id)) from akj_call_statistics t join akj_call_user a on t.call_user_id = a.id where a.is_delete=0 ";

		List<Object> list2 = new ArrayList<Object>();
		String where = this.getwhere(staticDate, assigneTo, leaderId, list2, clueGroupId);
		hql2 += where + " group by t.call_user_id ";
		hql2 = "select * from ("+hql2+") t1 order by t1.callTime desc ";
		countsql2 += where + "";
		return query(hql2, countsql2, list2, pageNo, pageSize);
	}

	private Page<AkjCallClueStatisticsVo> query(String hql, String countsql, List<Object> params, int pageNo,
			int pageSize) {
		Page<AkjCallClueStatisticsVo> list = hibernateTemplate
				.execute(new HibernateCallback<Page<AkjCallClueStatisticsVo>>() {

					@Override
					public Page<AkjCallClueStatisticsVo> doInHibernate(Session session) throws HibernateException {
						// TODO Auto-generated method stub
						NativeQuery query = session.createNativeQuery(hql);
						Query countquery = session.createNativeQuery(countsql);
						for (int i = 0; i < params.size(); i++) {
							query.setParameter(i + 1, params.get(i));
							countquery.setParameter(i + 1, params.get(i));
						}
						addScalar(query);
						List<AkjCallClueStatisticsVo> data = query
								.setResultTransformer(Transformers.aliasToBean(AkjCallClueStatisticsVo.class))
								.setMaxResults(pageSize).setFirstResult((pageNo - 1) * pageSize).getResultList();
						BigInteger countBig = new BigInteger("0");
						try {
							
							countBig = (BigInteger) countquery.getSingleResult();
						}catch (Exception e) {
							// TODO: handle exception
						}

						PageRequest pageRequest = PageUtils.buildPageRequestNoSort(pageNo, pageSize);
						Page<AkjCallClueStatisticsVo> page = new PageImpl<AkjCallClueStatisticsVo>(data, pageRequest,
								countBig.intValue());
						return page;

					}

				});
		return list;
	}
}
